package com.edu.dao;

import com.edu.bean.SubjectInfo;
import com.edu.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SubjectInfoDao {

    /**
     * 检查时间段是否已被占用
     */
    public boolean isTimeSlotOccupied(String classroom, Integer weekDay, String classTime, Integer subjectId) {
        String sql = "SELECT COUNT(*) FROM subject_info WHERE classroom = ? AND week_day = ? AND class_time = ?";
        if (subjectId != null) {
            sql += " AND subject_id != ?";
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, classroom);
            stmt.setInt(2, weekDay);
            stmt.setString(3, classTime);
            if (subjectId != null) {
                stmt.setInt(4, subjectId);
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 添加学科信息
     */
    public boolean add(SubjectInfo subject) {
        String sql = "INSERT INTO subject_info (subject_name, teacher_id, classroom, week_day, class_time, create_time) " +
                "VALUES (?, ?, ?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, subject.getSubjectName());
            stmt.setInt(2, subject.getTeacherId());
            stmt.setString(3, subject.getClassroom());
            stmt.setInt(4, subject.getWeekDay());
            stmt.setString(5, subject.getClassTime());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新学科信息
     */
    public boolean update(SubjectInfo subject) {
        String sql = "UPDATE subject_info SET subject_name = ?, teacher_id = ?, classroom = ?, " +
                "week_day = ?, class_time = ?, update_time = NOW() WHERE subject_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, subject.getSubjectName());
            stmt.setInt(2, subject.getTeacherId());
            stmt.setString(3, subject.getClassroom());
            stmt.setInt(4, subject.getWeekDay());
            stmt.setString(5, subject.getClassTime());
            stmt.setInt(6, subject.getSubjectId());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除学科信息
     */
    public boolean delete(Integer subjectId) {
        String sql = "DELETE FROM subject_info WHERE subject_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, subjectId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 查询所有学科信息（包含教师姓名）
     */
    public List<SubjectInfo> findAll() {
        String sql = "SELECT s.*, u.real_name as teacher_name FROM subject_info s " +
                "LEFT JOIN user u ON s.teacher_id = u.user_id ORDER BY s.subject_id DESC";
        List<SubjectInfo> subjectList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                subjectList.add(convertToSubjectInfo(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return subjectList;
    }

    /**
     * 根据ID查询学科信息
     */
    public SubjectInfo findById(Integer subjectId) {
        String sql = "SELECT s.*, u.real_name as teacher_name FROM subject_info s " +
                "LEFT JOIN user u ON s.teacher_id = u.user_id WHERE s.subject_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, subjectId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToSubjectInfo(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 将ResultSet转换为SubjectInfo对象
     */
    private SubjectInfo convertToSubjectInfo(ResultSet rs) throws SQLException {
        SubjectInfo subject = new SubjectInfo();
        subject.setSubjectId(rs.getInt("subject_id"));
        subject.setSubjectName(rs.getString("subject_name"));
        subject.setTeacherId(rs.getInt("teacher_id"));
        subject.setClassroom(rs.getString("classroom"));
        subject.setWeekDay(rs.getInt("week_day"));
        subject.setClassTime(rs.getString("class_time"));
        subject.setCreateTime(rs.getTimestamp("create_time"));
        subject.setUpdateTime(rs.getTimestamp("update_time"));
        subject.setTeacherName(rs.getString("teacher_name"));
        return subject;
    }

    /**
     * 分页查询学科信息
     */
    public List<SubjectInfo> findByPage(int start, int pageSize, String search) {
        String sql = "SELECT s.*, u.real_name as teacher_name FROM subject_info s " +
                "LEFT JOIN user u ON s.teacher_id = u.user_id " +
                "WHERE 1=1 ";
        if (search != null && !search.trim().isEmpty()) {
            sql += "AND s.subject_name LIKE ? ";
        }
        sql += "ORDER BY s.subject_id DESC LIMIT ?, ?";
        List<SubjectInfo> subjectList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            int paramIndex = 1;
            if (search != null && !search.trim().isEmpty()) {
                stmt.setString(paramIndex++, "%" + search + "%");
            }
            stmt.setInt(paramIndex++, start);
            stmt.setInt(paramIndex, pageSize);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                subjectList.add(convertToSubjectInfo(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return subjectList;
    }

    /**
     * 查询总记录数
     */
    public int findTotalCount(String search) {
        String sql = "SELECT COUNT(*) FROM subject_info WHERE 1=1 ";
        if (search != null && !search.trim().isEmpty()) {
            sql += "AND subject_name LIKE ? ";
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            if (search != null && !search.trim().isEmpty()) {
                stmt.setString(1, "%" + search + "%");
            }
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<SubjectInfo> findList(int start, int limit, String search) {
        List<SubjectInfo> list = new ArrayList<>();
        String sql = "SELECT s.*, u.real_name as teacher_name FROM subject_info s " +
                "LEFT JOIN user u ON s.teacher_id = u.user_id " +
                "WHERE 1=1 ";
        if (search != null && !search.trim().isEmpty()) {
            sql += "AND s.subject_name LIKE ? ";
        }
        sql += "ORDER BY s.subject_id DESC LIMIT ?, ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            int paramIndex = 1;
            if (search != null && !search.trim().isEmpty()) {
                stmt.setString(paramIndex++, "%" + search + "%");
            }
            stmt.setInt(paramIndex++, start);
            stmt.setInt(paramIndex, limit);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    list.add(convertToSubjectInfo(rs));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int count(String search) {
        int count = 0;
        String sql = "SELECT COUNT(*) FROM subject_info WHERE 1=1 ";
        if (search != null && !search.trim().isEmpty()) {
            sql += "AND subject_name LIKE ? ";
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            if (search != null && !search.trim().isEmpty()) {
                stmt.setString(1, "%" + search + "%");
            }
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    count = rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}